<?php
function cleanData(&$str)
{
    if($str == 't') $str = 'TRUE';
    if($str == 'f') $str = 'FALSE';
    if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
      $str = "'$str";
    }
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
    $str = iconv("UTF-8", "gb2312", $str);
    //$str = mb_convert_encoding($str, 'UTF-8', 'gb2312');
    //$str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
}
   
class ZendX_Controller_Doctrinext extends Zend_Controller_Action
{
	protected $qb = NULL;
	protected $model = null;
	
    
	public function indexAction()
    {    	
    	$this->view->qb = $this->qb;
    }
    
    public function addAction()
    {
    	if ($this->getRequest()->isPost()) 
        {
            $formData = $this->getRequest()->getPost();
    	             
            $entity = new $this->model;
            
            $entity->fromArray($formData);
                        
            $registry = Zend_Registry::getInstance();
        	$em = $registry->entityManager;
        	$em->persist($entity);
            $em->flush();
            
           	return $this->_helper->json(array('success' => true, 'id'=>$entity->id));
        }
        
    	$fixedmanytoone = array();
        $owner = $this->getRequest()->getParam('owner');
        $ownerkey = $this->getRequest()->getParam('ownerkey');
        if ($owner) {
        	$fixedmanytoone[$owner] = $ownerkey;
        }
        $this->view->fixedmanytoone = $fixedmanytoone;
    }
    public function editAction()
    {
    	$this->view->id = $this->_getParam('id', 0);
    	
    	$registry = Zend_Registry::getInstance();
        $em = $registry->entityManager;
     	if ($this->getRequest()->isPost()) {
            $formData = $this->getRequest()->getPost();
            $id = $this->getRequest()->getParam('id');
            
           	$entity = $em->find($this->model, $id);
            $entity->fromArray($formData);         	
            
            $em->persist($entity);
            $em->flush();
            
           return $this->_helper->json(array('success' => true));
        }
        
        $fixedmanytoone = array();
        $owner = $this->getRequest()->getParam('owner');
        $ownerkey = $this->getRequest()->getParam('ownerkey');
        if ($owner) {
        	$fixedmanytoone[$owner] = $ownerkey;
        }
        $this->view->fixedmanytoone = $fixedmanytoone;
    }
 	public function deleteAction ()
    {    	
        $result = 'false';
        if ($this->getRequest()->isPost()) {
            $id = $this->getRequest()->getParam('id');
            
            $registry = Zend_Registry::getInstance();
        	$em = $registry->entityManager;
        	
        	$entity = $em->find($this->model, $id);      	
        	
            $em->remove($entity);        
                        
            $em->flush();        	
                        
            $result = 'true';
        }
        return $this->_helper->json(array('success' => $result));
    }
    
	public function gridjsonAction()
	{    	
		$start = $this->getRequest()->getParam('start', 0);
    	$limit = $this->getRequest()->getParam('limit', 10);
    	$filter = $this->getRequest()->getParam('filter', array());
    	
		$get = $this->getRequest()->getParam('get', null);			
		$withkey = $this->getRequest()->getParam('withkey', null);  
		$notlinkedto = $this->getRequest()->getParam('notlinkedto');	
		$linkedto = $this->getRequest()->getParam('linkedto');
		
		if (! is_null($notlinkedto) ||
			! is_null($linkedto)) 
		{
			return $this->linkgrid($get, $notlinkedto, $linkedto, $withkey, $start, $limit, $filter);
		}
		        
		if ($this->getRequest()->getParam('export') == 'all') {
        	$result = ZendX_Service_Queryresult::getQueryResult($this->qb, array(), $filter);
        	return $this->exportXls($result['source']);
        }
        
        $param = array("start" => $start, "limit" => $limit);
        
        $result = ZendX_Service_Queryresult::getQueryResult($this->qb, $param, $filter);
        
        if ($this->getRequest()->getParam('export') == 'page') {
        	return $this->exportXls($result['source']);
        }
        
        return $this->_helper->json($result);
	}
	
	private function linkgrid($get, $notlinkedto, $linkedto, $withkey, $start, $limit, $filter)
	{
		$registry = Zend_Registry::getInstance();	
		$entityManager = $registry->entityManager;
		
		$getTable = $entityManager->getClassMetadata($get)->getTableName();
		$link = is_null($notlinkedto) ? $linkedto : $notlinkedto;
		
		$linkTable = $entityManager->getClassMetadata($link)->getTableName();
		$joinTable = $link . "_" . $get;
		$linkcol = $link . "_id";
			
		$sql = "";
		
		if (! is_null($notlinkedto)) 
		{		
			$sql = "SELECT d.*
					from {$getTable} d
					where d.id not IN
					(
					select j.{$get}_id
					from {$joinTable} j, {$linkTable} l
					where j.{$linkcol} = l.id
					and l.id = {$withkey}
					)";
		}
		if (! is_null($linkedto)) {
			$sql = "SELECT d.*
					from {$getTable} d
					where d.id IN
					(
					select j.{$get}_id
					from {$joinTable} j, {$linkTable} l
					where j.{$linkcol} = l.id
					and l.id = {$withkey}
					)";
		}	
		
		if ($this->getRequest()->getParam('export') == 'all') {
        	$result = $this->getQueryResult($sql, $filter, $start, null);
        	return $this->exportXls($result['source']);
        }
        
         $result = $this->getQueryResult($sql, $filter, $start, $limit);
        
        if ($this->getRequest()->getParam('export') == 'page') {
        	return $this->exportXls($result['source']);
        }
        
        return $this->_helper->json($result);
	}
	
	private function getQueryResult($sql, $filter = array(), $start, $limit)
	{
		$db = Zend_Controller_Front::getInstance()->getParam('bootstrap')->getPluginResource('db')->getDbAdapter();
		
		foreach ($filter as $v)
		{
			if($v['data']['type'] == 'string')
        	{
        		$sql .= " and {$v['field']} like '{$v['data']['value']}%' ";        		
        	}
        	else if($v['data']['type'] == 'numeric' || $v['data']['type'] == 'date')
        	{
        		$compare = null;
        		if($v['data']['comparison'] == 'lt')
        		{
        			$compare = '<';
        			
        		}
        		else if($v['data']['comparison'] == 'gt')
        		{
        			$compare = '>';
        		}
        		else if($v['data']['comparison'] == 'eq')
        		{
        			$compare = '=';
        		}
        		
        		if (!is_null($compare))
        		{   
        		    $filterParam = $v['data']['value'];
        			if ($v['data']['type'] == 'date')
        			{
        				$format = 'm/d/Y';
						$date = DateTime::createFromFormat($format, $filterParam);
        				$filterParam = $date->format('Y-m-d');
        			}

        			$sql .= " and {$v['field']} {$compare} {$filterParam} ";        			
        		}        		
        	}
		}
		
		$totalcount = $db->fetchOne($sql);
		
		if (!is_null($limit)) {
			$sql .= 'limit '.$start.','.$limit;
		}
		
		$result = $db->fetchAll($sql);
		
		return array('totalcount' => $totalcount, 'source' => $result);
	}
	    
    public function moveinAction()
    {
    	$add = $this->getRequest()->getParam('add', null);
    	$to = $this->getRequest()->getParam('to', null);
		$of = $this->getRequest()->getParam('of', null);
		$withkey = $this->getRequest()->getParam('withkey', null);
    	
    	$data = $this->getRequest()->getParam("data");   	
    	$data = json_decode($data);
    	
    	$registry = Zend_Registry::getInstance();
        $em = $registry->entityManager;
        
        $func = "add". $add;
        $ofObj = $em->find($of, $withkey);
    	foreach ($data as $v)
    	{
    		$addObj = $em->find($add, $v);		
    		$ofObj->$func($addObj);
    	}
    	$em->flush();
    	
    	return $this->_helper->json(
        array('sucess' => true));
    }
    
    public function moveoutAction()
    {
    	$remove = $this->getRequest()->getParam('remove', null);
    	$from = $this->getRequest()->getParam('from', null);
		$of = $this->getRequest()->getParam('of', null);
		$withkey = $this->getRequest()->getParam('withkey', null);
		
    	$data = $this->getRequest()->getParam("data"); 
    	$data = json_decode($data);
    	
    	$registry = Zend_Registry::getInstance();
        $em = $registry->entityManager;
        
        $func = "get". ucfirst($from);
        $ofObj = $em->find($of, $withkey);
    	foreach ($data as $v)
    	{
    		$removeObj = $em->find($remove, $v);
    		$ofObj->$func()->removeElement($removeObj);
    	}
    	$em->flush();
    	    	
    	return $this->_helper->json(
        array('sucess' => true));    	
    }
    
    private function exportXls($data)
    {
    	$filename ="excelreport.xls";
    	
    	header('Content-type: application/vnd.ms-excel"');
		header('Content-Disposition: attachment; filename='.$filename);
		
    	$firstRow = false;
    	foreach ($data as $row){
    		if(!$firstRow) {
      			// display field/column names as first row
      			echo implode("\t", array_keys($row)) . "\r\n";
      			$firstRow = true;
    		}
    		array_walk($row, 'cleanData');
    		echo implode("\t", array_values($row)) . "\r\n";
    	}
    	exit();
    }   
}